package com.red.wood.impt;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;

import com.red.wood.base.BaseTest;
import com.red.wood.common.springjdbc.CommonDao;

//@Ignore
public class ImportDemo extends BaseTest{

	private static final Log log = LogFactory.getLog(ImportDemo.class);
	
	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private CommonDao commonDao;
	
	@Test
	public void importData(){
		try {
			Workbook workbook = null;
			String filename = "D:"+File.separator+"data"+File.separator+"website_location.xlsx"; 
			String fileType = ".xlsx";
			if(StringUtils.equals(fileType, ".xlsx")){
				workbook = new XSSFWorkbook(new FileInputStream(filename));
			}else{
				workbook = new HSSFWorkbook(new FileInputStream(filename));
			}
			readExcel(workbook,fileType);
		}catch(Exception e) {
			if(log.isErrorEnabled()) {
				log.error(e.getMessage(),e);
			}
		}
	}

	public void readExcel(Workbook workbook,String fileType){
		StringBuffer validInfo = null;
		// 循环工作表Sheet
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Sheet sheet = workbook.getSheetAt(numSheet);
			String tableName = sheet.getSheetName();//约定sheet页名称为表名称
			if (sheet == null || sheet.getRow(1) == null) {
				continue;
			}
			// 循环行Row,第一行为数据中字段，第二行为字段头，第三行开始为数据
			// 获取字段名
			Row rowOne = sheet.getRow(0);// 获取数据库中字段
			Row rowName = sheet.getRow(1);
			int columns = rowName.getPhysicalNumberOfCells();
			boolean validFlag = true;
			for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
				try {
					validInfo = new StringBuffer();
					Row row = sheet.getRow(rowNum);
					if (row == null) {
						continue;
					}
					// 循环列Cell 如： 0 策略名称 1 指定生效时间 ...
					Map<String, Object> param = new HashMap<String, Object>();
					for (int i = 0; i < columns; i++) {
						String name = getValue(rowOne.getCell(i));
						if (StringUtils.isBlank(name)) {
							//提示用户EXECL中字段有为空的
							validInfo.append(name+"不为空");
						}
						Cell cell = row.getCell(i);
						if(cell == null) {
							System.err.println("第"+(rowNum+1)+"行，"+name+"值为空！");
							param.put(name,"");
							continue;
						}
						//防止数字变为变成科学计数法，默认都设置为string，若有小数在特殊处理
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						String value = StringUtils.trim(getValue(cell));
						if(StringUtils.isBlank(value)) {
							System.err.println("第"+(rowNum+1)+"行，"+name+"值为空！");
						}
						param.put(name, value);
					}
					list.add(param);
					
					boolean flag  = valid(validInfo,workbook,columns,row,fileType);
					if(!flag) {
						validFlag = false;
					}
				}catch(Exception e) {
					if(log.isErrorEnabled()) {
						log.error(e.getMessage(), e);
					}
				}
			}
			//校验
			if(validFlag) {
				try {
					if(list != null && list.size() > 0) {
						String delSql = "delete from "+tableName;
						commonDao.deleteTableData(delSql);
						
						Map<String,Object> map = list.get(0);
						Set<String> keys = map.keySet();
						StringBuffer keyBuffer = new StringBuffer();
						StringBuffer valuesBuffer = new StringBuffer();
						for(String key : keys) {
							keyBuffer.append(key).append(",");
							valuesBuffer.append("?").append(",");
						}
						String column = keyBuffer.toString().substring(0,keyBuffer.length()-1);
						String values = valuesBuffer.toString().substring(0,valuesBuffer.length()-1);
						String sql = "insert into "+tableName+"("+column+") values("+values+")";
						System.out.println(sql);
						
						commonDao.insertBatchTableData(list, sql, column,5000);
					}
				}catch(Exception e) {
					if(log.isErrorEnabled()) {
						log.error(e.getMessage(),e);
					}
				}
			}
		}
	}

	public boolean valid(StringBuffer validInfo, Workbook workbook,
			int columns, Row row, String fileType) throws IOException {
		boolean validFlag = true;
		Cell addCell = null;
		// 如果存在要校验失败信息，则将校验结果追加到源文件中
		if (validInfo.length() > 0) {
			validFlag = false;
			// 生成红色字体样式
			CellStyle style1 = workbook.createCellStyle();
			Font font = workbook.createFont();
			font.setColor(HSSFColor.RED.index);
			font.setFontHeightInPoints((short) 11);
			style1.setFont(font);

			// 在当前行的colNum列上创建单元格
			addCell = row.createCell(columns);
			// 添加内容至单元格
			addCell.setCellValue(validInfo.toString());
			// 设置红色字体
			addCell.setCellStyle(style1);
		}
		// 如果存在要校验失败信息，则将校验结果追加到源文件中，给客户端下载
		if (!validFlag) {
			// 新建一输出文件流
			// String path = CommonUtils.getDataTempPath();
			String path = "D:" + File.separator + "err" + File.separator;
			File tempFile = new File(path);
			if (!tempFile.exists()) {
				tempFile.mkdirs();
			}
			String fileName = mathRandom(10) + fileType;
			path = path + fileName;
			File fullPath = new File(path);
			if (fullPath.exists()) {
				fullPath.delete();
			}
			FileOutputStream fOut = new FileOutputStream(fullPath);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			workbook.close();
			Map<String, Object> retMap = new HashMap<String, Object>();
			// 返回用户导入的文件名给客户端下载使用
			retMap.put("filePath", path);
			retMap.put("filename", fileName);
		}
		return validFlag;
	}
	
	/**
	 * 得到Excel表中的值
	 * @param cell Excel中的每列
	 * @return Excel中每列的值
	 */
	@SuppressWarnings("static-access")
	public String getValue(Cell cell) {
		String value = "";
		if (cell != null) {
			if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
				value = String.valueOf(cell.getBooleanCellValue());// 返回布尔类型的值
			} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
				value = String.valueOf(cell.getNumericCellValue());// 返回数值类型的值
			} else {
				value = String.valueOf(cell.getStringCellValue());// 返回字符串类型的值
			}
		}
		return value;
	}
	
	public static final String mathRandom(final long length) {
		double len = Math.pow(10D, length);
		long result = (long) (len * Math.random());

		// 补齐随机数长度
		while (len / result > 10) {
			result *= 10;
		}
		return Long.toString(result);
	}
}
